'''测试目的
1.每一条write操作, 执行commit
2.批量write操作, 执行commit
对比耗时

3.csv 写文件耗时,和文件大小对比
'''
import time
import random
import sqlite3

def db_write_async_commit(cur, count: int):
    start_time = time.time()
    while count > 0:
        cur_time = round(time.time(), 3)
        cost_time = random.uniform(0.010, 0.30)
        cur.execute("insert into cost_time values(?,?)", (cur_time, cost_time))
        count -= 1
    con.commit()
    end_time = time.time()
    print(f"异步commit {count} cost_time: ", end_time - start_time)


def db_write_sync_commit(cur, count: int):
    start_time = time.time()
    while count > 0:
        cur_time = round(time.time(), 3)
        cost_time = random.uniform(0.010, 0.30)
        cur.execute("insert into cost_time values(?,?)", (cur_time, cost_time))
        count -= 1
        con.commit()
    end_time = time.time()
    print("同步commit cost_time: ", end_time - start_time)


def csv_DB_write(count: int):
    import csv
    start_time = time.time()
    with open("cost_time.csv",'w',newline='') as fp:
        csv_writer = csv.writer(fp)
        csv_writer.writerow(["time","cost_time"])
        while count > 0:
            cur_time = round(time.time(), 3)
            cost_time = random.uniform(0.010, 0.30)
            csv_writer.writerow([cur_time,cost_time])
            count -= 1
    end_time = time.time()
    print(f"csv 写文件 {count} cost_time: ", end_time - start_time)

if __name__ == "__main__":
    con = sqlite3.connect("cost_time.db")
    cur = con.cursor()
    cur.execute(
        """create table cost_time(
        time real,
        cost_time real
        )"""
    )
    count = 1000000
    db_write_async_commit(cur, count)
    # db_write_sync_commit(cur)
    csv_DB_write(count)
    con.close()

''' 测试1: count = 100
异步commit cost_time:  0.00793313980102539
同步commit cost_time:  0.8098654747009277
csv DB写文件 cost_time:  0.00037384033203125
'''

''' 测试1: count = 1000000
异步commit 0 cost_time:  2.4742929935455322
csv 写文件 0 cost_time:  2.472313642501831

du -h cost_time.db cost_time.csv
25M     cost_time.db
35M     cost_time.csv
'''

''' 总结
1.sqlite 批量写之后, 再执行commit, 性能更高
2.csv 写文件, 性能更高, 但生成文件体积大
'''